import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
import pymysql  # 新增：MySQL连接库
from matplotlib.gridspec import GridSpec, GridSpecFromSubplotSpec
from sqlalchemy import create_engine  # 新增：用于pandas写入MySQL

# 设置中文显示
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

# 新增：MySQL数据库配置（请替换为你的数据库信息）
MYSQL_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'gmall-bi',
    'port': 3306,
    'charset': 'utf8mb3'
}

# 新增：创建MySQL连接引擎
engine = create_engine(
    f"mysql+pymysql://{MYSQL_CONFIG['user']}:{MYSQL_CONFIG['password']}@"
    f"{MYSQL_CONFIG['host']}:{MYSQL_CONFIG['port']}/{MYSQL_CONFIG['database']}?charset={MYSQL_CONFIG['charset']}"
)


def save_metrics_to_mysql(conn, shop_id=1):
    """将关键指标存储到MySQL数据库"""
    # 1. 获取关键指标数据（与看板中"关键指标概览"一致）
    latest_date = pd.read_sql(
        f"SELECT MAX(date) as max_date FROM traffic_overview WHERE shop_id = {shop_id}",
        conn
    ).iloc[0, 0]

    metrics_df = pd.read_sql(f'''
        SELECT 
            total_visitors,
            total_pageviews,
            bounce_rate,
            conversion_rate,
            payment_count,
            payment_amount,
            date
        FROM traffic_overview
        WHERE shop_id = {shop_id} AND date = '{latest_date}'
    ''', conn)

    # 添加shop_id字段
    metrics_df['shop_id'] = shop_id

    # 2. 获取流量趋势数据（与看板中"流量总览趋势"一致）
    traffic_df = pd.read_sql(f'''
        SELECT date, total_visitors, conversion_rate, payment_amount 
        FROM traffic_overview 
        WHERE shop_id = {shop_id} 
        ORDER BY date
    ''', conn)
    traffic_df['date'] = pd.to_datetime(traffic_df['date'])
    # 计算7天移动平均
    traffic_df['visitors_7d'] = traffic_df['total_visitors'].rolling(window=7, min_periods=1).mean()
    # 添加shop_id字段
    traffic_df['shop_id'] = shop_id

    # 3. 写入MySQL（如果数据已存在则替换）
    try:
        # 写入关键指标表（if_exists='replace'确保数据更新）
        metrics_df[['shop_id', 'date', 'total_visitors', 'total_pageviews',
                   'bounce_rate', 'conversion_rate', 'payment_count', 'payment_amount']].to_sql(
            name='dashboard_metrics',
            con=engine,
            if_exists='append',
            index=False,
            method='multi'
        )

        # 写入流量趋势表
        traffic_df[['shop_id', 'date', 'total_visitors', 'visitors_7d',
                   'conversion_rate', 'payment_amount']].to_sql(
            name='dashboard_traffic_trend',
            con=engine,
            if_exists='append',
            index=False,
            method='multi'
        )

        print(f"成功将店铺ID={shop_id}的指标数据写入MySQL数据库")
    except Exception as e:
        print(f"写入MySQL失败：{str(e)}")


def create_dashboard(conn, shop_id=1):


    # 生成看板后调用存储函数
    save_metrics_to_mysql(conn, shop_id)  # 新增：存储指标到MySQL


# 示例：执行代码
if __name__ == "__main__":
    # 连接原SQLite数据库（假设数据来源是SQLite）
    sqlite_conn = sqlite3.connect('taobao_dashboard.db')  # 替换为你的SQLite数据库路径
    create_dashboard(sqlite_conn, shop_id=1)
    sqlite_conn.close()